/*
Given a hierarchy key, returns everything from DocumentMain except the blob (for security and performance reasons)
for the Document associated with that Hierarchy element.  Also includes the DocumentTypeName, DocumentTypeDesc,
and DocumentIconURL from the associated DocumentTypeRef record.

If publishedOnly = 1 it will only get published documents.  Otherwise, it gets the latest version, published or working.
It won't get documents with any other status

NOTE: Security is bypassed since the call requires the key (e.g., a reference from an object to which the user
has access) and the sensitive part, the blob, is not returned.
*/
CREATE PROC [dbo].[asi_DocumentMainByHierarchyKey]
   @hierarchyKey uniqueidentifier,
   @publishedOnly bit = 0
AS
BEGIN
   SELECT a.DocumentKey,
          a.DocumentVersionKey,
          a.DocumentStatusCode,
          a.DocumentName,
          a.AlternateName,
          a.DocumentDescription,
          a.DocumentTypeCode,
          a.IsSystem,
          a.ContainsChildrenFlag,
          a.AccessKey,
          a.DefaultChildAccessKey,
          a.StatusUpdatedOn,
          a.StatusUpdatedByUserKey,
          a.CreatedOn,
          a.CreatedByUserKey,
          a.UpdatedOn,
          a.UpdatedByUserKey,
          b.DocumentTypeName,
          b.DocumentTypeDesc,
          b.DocumentIconURL
     FROM [dbo].[DocumentMain] a INNER JOIN [dbo].[DocumentTypeRef] b on a.DocumentTypeCode = b.DocumentTypeCode
          INNER JOIN [dbo].[Hierarchy] c ON a.DocumentVersionKey = c.UniformKey
    WHERE c.HierarchyKey = @hierarchyKey
      AND ((a.DocumentStatusCode IN (10,20,30,70) AND @publishedOnly = 0)
       OR (a.DocumentStatusCode IN (40,60)
      AND (@publishedOnly = 1
       OR NOT EXISTS (
          SELECT 1
            FROM [dbo].[DocumentMain] d
           WHERE d.DocumentVersionKey = a.DocumentVersionKey
             AND d.DocumentStatusCode IN (10,20,30,70)))))
END

GO